import java.io.File;
import java.sql.*;

import ecologylab.xml.ElementState;
import ecologylab.xml.TranslationScope;
import ecologylab.xml.XMLTranslationException;

import java.util.ArrayList;
import xmlParser.ColField;
import xmlParser.ColFieldType;
import xmlParser.FieldInteger;
import xmlParser.FieldString;
import xmlParser.Record;
import xmlParser.Table;
import xmlParser.TypedRecord;
import xmlParser.TypedTable;
import xmlParser.xmlParserTranslationScope;
public class RunDB {
	Connection connection;
	   Statement statement;
	   Statement statement2;

	   public void loadDriver() throws ClassNotFoundException{
	      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	   }

	 //edited here by Mark
	   public void makeConnection(String name) throws SQLException {
	      String driver = "jdbc:odbc:";
	      if(name == null)
		   {
			   connection=
			         DriverManager.getConnection("jdbc:odbc:student");
			   return;
		   }
		   connection=
	         DriverManager.getConnection(driver+name);//"jdbc:odbc:student");
		   
	   }

	   public void buildStatement() throws SQLException {
	      statement = connection.createStatement();
	      statement2 = connection.createStatement();
	   }

	   	//Mark's comment: added to allow to create DB.
	   public void createDB(GUIDBPanel screen, String tName, ArrayList<String> subsetField)
	   {
		   //need to create the data base with appropriate commands.
		   
		   int i;
		   
		   //create only one table - according to Chris
		   String text = "CREATE TABLE ";
		   text += tName;
		   text += " (";
		
		   //find out the size of the subset
		   int size = subsetField.size();
		   //create the fields for that table
		   for(i=0;i < size; i++)
		   {
			   if (i == size-1)
			   {
				   text += subsetField.get(i);//fTypeName[j] + ", ";
			   }
			   else
			   {
				   text += subsetField.get(i) + ", ";
			   }
		   }
		   text += " ";
			   
		   text += ");";
		   
		   screen.printToScreen(text);
		   screen.printToScreen("\n");
			   
		   //send the statement out
		   try
		   {
			   statement.executeUpdate(text);
		   }
		   catch (SQLException ex)
		   {
			   System.err.println("SQLexception: " + ex.getMessage());
		   }
	   }
	   
	   /**
	    * sql execution without displaying the sql statement on the display screen.
	    * @param query
	    * @param setXML
	    * @throws SQLException
	    */
	   public void executeQuery(String query, boolean setXML) throws SQLException {

		      boolean foundResults =
		         statement.execute(query);
		      System.out.println(query);
		      if(foundResults){
		         ResultSet set = statement.getResultSet();
		         
		         statement2.execute(query);
		         ResultSet anotherset = statement2.getResultSet();
		         if(set!=null) {
		        	 //check to see if we need to output to xml or not
		        	 if (setXML == true){
		        		 displayResults(set);
		        	 }
		        	 //displayonGUI(screen,anotherset);
		         }
		      }else {
		         connection.close();
		      }
		   }
	   
	   public void executeQuery(GUI screen, String query, boolean setXML) throws SQLException {

	      boolean foundResults =
	         statement.execute(query);
	      
	      System.out.println(query);
	      if(foundResults){
	         ResultSet set = statement.getResultSet();
	         
	         statement2.execute(query);
	         ResultSet anotherset = statement2.getResultSet();
	         if(set!=null) {
	        	 //check to see if we need to output to xml or not
	        	 if (setXML == true){
	        		 displayResults(set);
	        	 }
	        	 displayonGUI(screen,anotherset);
	         }
	      }else {
	         connection.close();
	      }
	   }
	  
	   public void displayonGUI(GUI screen, ResultSet rs) throws SQLException {
	      ResultSetMetaData metaData = rs.getMetaData();
	      int columns=metaData.getColumnCount();
		   String resulttext = "";
		   int isfirst=1;
		   while(rs.next()){
	             if(isfirst==1) {
	            	 for(int i=1;i<=columns;++i) {
	            		 resulttext += metaData.getColumnName(i)+", ";
	            	 }
	            	 resulttext+="\n";
	            	 isfirst=0;
	             }
		         for(int i=1;i<=columns;++i) {
		            resulttext+=rs.getString(i)+", ";
		         }
		         //Without using the ecologylabFundamental lib.
		         resulttext+="\n";
		   }
		   screen.printToScreen(resulttext);
		   
	   }
	   

	   void displayResults(ResultSet rs) throws SQLException {
	      ResultSetMetaData metaData = rs.getMetaData();
	      int columns=metaData.getColumnCount();
	      String resulttext="";

	      //Bharat
	      Table table = new Table();
	      TypedTable typedTable = new TypedTable();
	      File sampleFile = new File("metadata.xml");
	      File sampleFile1 = new File("readfrommetadata.xml");
	      int isfirst=1;
	      while(rs.next()){
	    	  Record record = new Record();
	    	  TypedRecord typedRecord = new TypedRecord();
	    	  
	    	 
	         for(int i=1;i<=columns;++i) {
	        	// System.out.println(rs.getString(i));
	         	 
	        	 if(isfirst == 1)
		    	  {
		    		  //populating the fieldTypes in table
	        		 ColFieldType colFieldType = new ColFieldType(metaData.getColumnName(i),
	        				 					sqlType(metaData.getColumnType(i)));
	        		 table.addfieldType(colFieldType);
	        		 
		    	  }
	        	 
	        	 //Populating the data structure for xml
	        	 
	             ColField colField = new ColField();
	             colField.setName(metaData.getColumnName(i));
	             colField.setValue(rs.getString(i));
	             record.addField(colField);
	             
	             //Populating the rightly typed data structure
	             switch(metaData.getColumnType(i))
	             {
	             case 12:/*VARCHAR*/
	            	 FieldString fieldString = new FieldString(metaData.getColumnName(i),
	            			 "VARCHAR", colField.getValue());
	            	 typedRecord.addField(fieldString);
	            	 break;
	             case 4:/*INTEGER*/
	            	 FieldInteger fieldInteger = new FieldInteger(metaData.getColumnName(i),
	            			 "INTEGER", Integer.parseInt(colField.getValue()));
	            	 typedRecord.addField(fieldInteger);
	            	 break;
	            	 //TODO! Have to code for other types
	             default: ;
	             }
	             
	         }
	         //Bharat
	         isfirst = 0;
	         table.addRecord(record);
	         typedTable.addRecord(typedRecord);
	         
	      }
	      
	      //Bharat
	      try {
	    	  //Writing to xmlfile metadata.xml
			table.writePrettyXML(sampleFile);
			
			//Reading from metadata.xml and writing into readfrommetadata.xml
			final TranslationScope TS = xmlParserTranslationScope.get();
			Table newTable = (Table) ElementState.translateFromXML(sampleFile,TS);
			newTable.writePrettyXML(sampleFile1);
			
			TypedTable typedTablefromxml = new TypedTable();
			//Traversing accross records
			for(Record record : newTable.getRecords())
			{
				TypedRecord typedRecordfromxml = new TypedRecord();
				for(ColField colField : record.getFields())
				{
					//TypedColField typedColField = new TypedColField();
					Integer fieldType = newTable.getFieldTypeInt(colField.getName());
					switch(fieldType)
					{
					case 12:/*VARCHAR*/
						FieldString fieldString = new FieldString(colField.getName(),
								"VARCHAR", colField.getValue());
						typedRecordfromxml.addField(fieldString);
						break;
					case 4:/*INTEGER*/
						FieldInteger fieldInteger = new FieldInteger(colField.getName(),
								"INTEGER", Integer.parseInt(colField.getValue()));
						typedRecordfromxml.addField(fieldInteger);
						break;
						//TODO! Have to code for other types
					default: ;
					}
				}//fields
				typedTablefromxml.addRecord(typedRecordfromxml);
			}//records
			System.out.println(typedTablefromxml);

		} catch (XMLTranslationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	   }
	   
	   private String sqlType(Integer type)
	   {
		   switch(type)
		   {
		   case 12:/*VARCHAR*/
			   return "VARCHAR";
		   case 4:/*INTEGER*/
			   return "INTEGER";
		   default: 
			   return "WRONG";
		   }
	   }
	   
	   private Integer sqlTypeInt(String type)
	   {
		   if(type.equals("VARCHAR"))
			   return 12;
		   else if(type.equals("INTEGER") || type.equals("INT"))
			   return 4;
		   return -1;
	   }

}
